"""step5_6_4_update_note.py

目的：
    更新 scene_item_ext 与 scene_item 备注 bz 字段

步骤概要：
    1. 准备 stable_serv 与 stable_medi 中的 备注 bz 映射
    2. 更新 scene_item_ext sie
        2.1. 如果有 bz 列，就先删除，再创建
        2.2. stable_serv 方面，用 sie.item_std_code 去匹配（=） stable_serv.item_j_code，获取 bz，如果有就更新
        2.3. stable_medi 方面，用 sie.item_std_code 去匹配（LIKE） {stable_medi.item_code_ptn}，获取 bz，如果有就更新
    3. 更新 scene_item si
        2.1. 如果有 bz 列，就先删除，再创建
        2.2. si 的 item_j_codes 是从 sie 聚合过来的（用|），所以直接用 sie 的 bz 去更新 si 的 bz，取MAX
运行：
   python STEP5干净表/step5_6_4_update.py
"""
from __future__ import annotations
import os
import sys
from pathlib import Path
from typing import List, Dict
import argparse
import pandas as pd
from sqlalchemy import text
from sqlalchemy.engine import Engine
import time
from threading import Lock

ROOT_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
if ROOT_DIR not in sys.path:
    sys.path.append(ROOT_DIR)

from config import create_db_engine
engine = create_db_engine()


# 进度锁，避免多线程输出互相覆盖
_progress_lock = Lock()


def print_inline(msg: str) -> None:
    """单行滚动显示进度信息。"""
    try:
        with _progress_lock:
            sys.stdout.write('\r' + ' ' * 120)
            sys.stdout.write('\r' + msg[:120])
            sys.stdout.flush()
    except Exception:
        print(msg)


def print_newline(msg: str = "") -> None:
    """输出换行提示。"""
    with _progress_lock:
        sys.stdout.write('\n' + msg + '\n')
        sys.stdout.flush()


# 时间函数
t0 = time.time()
def elapsed() -> str:
    """返回自脚本启动以来的耗时 (HH:MM:SS)。
    用于将原先的 [INFO]/[START]/[DONE] 等等级标签替换成实时耗时。
    """
    
    # 实际时间，yyy-mm-dd hh:mm:ss
    timeStr = time.strftime("%H:%M:%S", time.localtime())
    
    # 已消耗时间 XX hours XX minutes XX.XX seconds
    delta = int(time.time() - t0)
    if delta < 60:
        return f"{timeStr} (+ {delta} sec)"
    elif delta < 3600:
        m, s = divmod(delta, 60)
        return f"{timeStr} (+ {m} min {s} sec)"
    elif delta < 86400:
        h, rem = divmod(delta, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {h} hour {m} min {s} sec)"
    else:
        d, rem = divmod(delta, 86400)
        h, rem = divmod(rem, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {d} day {h} hour {m} min {s} sec)"

# ------------------------------
#  读取配置与环境
# ------------------------------
def alter_sie(engine: Engine) -> None:
    """修改 scene_item_ext，添加 bz 列"""
    with engine.connect() as conn:
        # 先删除 bz 列（如果存在）
        print("Altering scene_item_ext table: dropping bz column if exists...")
        conn.execute(text("""
            BEGIN
                EXECUTE IMMEDIATE 'ALTER TABLE SCENE_ITEM_EXT DROP COLUMN BZ';
            EXCEPTION
                WHEN OTHERS THEN
                    IF SQLCODE != -00904 THEN
                        RAISE;
                    END IF;
            END;
        """))
        conn.commit()
        # 再添加 bz 列
        print("Altering scene_item_ext table: adding bz column...")
        conn.execute(text("""
            ALTER TABLE SCENE_ITEM_EXT ADD (BZ VARCHAR2(4000))
        """))
        conn.commit()
                          

def update_sie_serv(engine: Engine) -> None:
    """更新 scene_item_ext 中的 备注 bz 字段，基于 stable_serv 的映射"""
    update_sql = text(f"""
        UPDATE SCENE_ITEM_EXT sie
        SET sie.bz = (
            SELECT ss.bz
            FROM STABLE_SERV ss
            WHERE sie.item_std_code = ss.item_j_code
              AND ss.bz IS NOT NULL
              -- AND ss.bz != ''
        )
        WHERE EXISTS (
            SELECT 1
            FROM STABLE_SERV ss2
            WHERE sie.item_std_code = ss2.item_j_code
              AND ss2.bz IS NOT NULL
              -- AND ss2.bz != ''
        )
    """)
    with engine.connect() as conn:
        print("Updating scene_item_ext bz from stable_serv...")
        result = conn.execute(update_sql)
        conn.commit()
        print(f"  - {result.rowcount} rows updated.")

def update_sie_medi(engine: Engine) -> None:
    """更新 scene_item_ext 中的 备注 bz 字段，基于 stable_medi 的映射"""
    update_sql = text(f"""
        UPDATE SCENE_ITEM_EXT sie
        SET sie.bz = (
            SELECT sm.bz
            FROM STABLE_MEDI sm
            WHERE sie.item_std_code LIKE sm.item_code_ptn||'%'
              AND sm.bz IS NOT NULL
              -- AND sm.bz != ''
        )
        WHERE EXISTS (
            SELECT 1
            FROM STABLE_MEDI sm2
            WHERE sie.item_std_code LIKE sm2.item_code_ptn||'%'
              AND sm2.bz IS NOT NULL
              -- AND sm2.bz != ''
        )
    """)
    with engine.connect() as conn:
        print("Updating scene_item_ext bz from stable_medi...")
        result = conn.execute(update_sql)
        conn.commit()
        print(f"  - {result.rowcount} rows updated.")

def alter_si(engine: Engine) -> None:
    """修改 scene_item，添加 bz 列"""
    with engine.connect() as conn:
        # 先删除 bz 列（如果存在）
        print("Altering scene_item table: dropping bz column if exists...")
        conn.execute(text("""
            BEGIN
                EXECUTE IMMEDIATE 'ALTER TABLE SCENE_ITEM DROP COLUMN BZ';
            EXCEPTION
                WHEN OTHERS THEN
                    IF SQLCODE != -00904 THEN
                        RAISE;
                    END IF;
            END;
        """))
        conn.commit()
        # 再添加 bz 列
        print("Altering scene_item table: adding bz column...")
        conn.execute(text("""
            ALTER TABLE SCENE_ITEM ADD (BZ VARCHAR2(4000))
        """))
        conn.commit()

def update_si_from_sie(engine: Engine) -> None:
    """逐条更新 scene_item 中的 bz 字段，并输出进度。"""

    print("Preparing scene_item updates from scene_item_ext...")
    with engine.connect() as conn:
        df_mapping = pd.read_sql(
            """
                SELECT item_j_code, MAX(bz) AS bz
                FROM SCENE_ITEM_EXT
                WHERE bz IS NOT NULL
                GROUP BY item_j_code
            """,
            conn,
        )

    if df_mapping.empty:
        print("  - No bz data found in scene_item_ext, skip scene_item updates.")
        return

    update_sql = text(
        """
            UPDATE SCENE_ITEM
            SET bz = :bz
            WHERE '|' || item_j_codes || '|' LIKE :pattern
        """
    )

    total = len(df_mapping)
    updated_rows = 0

    with engine.begin() as conn:
        for idx, row in df_mapping.iterrows():
            item_j_code = str(row['item_j_code'])
            if not item_j_code:
                continue
            params = {
                "bz": row["bz"],
                "pattern": f"%|{item_j_code}|%",
            }
            result = conn.execute(update_sql, params)
            if result.rowcount:
                updated_rows += result.rowcount

            if (idx + 1) % 20 == 0 or (idx + 1) == total:
                print_inline(
                    f"[{elapsed()}] Updating scene_item bz [{idx + 1}/{total}] - affected rows: {updated_rows}"
                )

    print_newline(f"scene_item bz updated. total affected rows: {updated_rows}")



# 主函数
if __name__ == "__main__":
    print(f"[{elapsed()}] Starting step5_6_4_update_note.py...")

    # 读取 stable_serv 与 stable_medi 中的 备注 bz 映射
    # df_stable_serv_bz = read_serv_bz(engine)
    # df_stable_medi_bz = read_medi_bz(engine)

    # 更新 scene_item_ext 的 bz 列
    alter_sie(engine)
    update_sie_serv(engine)
    update_sie_medi(engine)
    print(f"[{elapsed()}] scene_item_ext bz update completed.")

    # 更新 scene_item 的 bz 列
    alter_si(engine)
    update_si_from_sie(engine)
    print(f"[{elapsed()}] scene_item bz update completed.")

    print(f"[{elapsed()}] step5_6_4_update_note.py completed.")

    